﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Wild2.Models.Exceptions;

namespace Wild2.Models.Blog
{
    public class Blog
    {
        public static List<Topic.TopicData> GetTopics()
        {
            return GetTopics(0);
        }

        public static void AddTopic(int userID, int blogID, String caption, String text, String longtext, String[] tags, bool isPublic, bool hidecomments)
        {
            if (caption.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Заголовок", 50);
            if (caption.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Заголовок", 3);
            if (text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Текст", 500);
            if (text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Текст", 3);
            if (longtext.Length > 4000) throw new Wild2.Models.Exceptions.TextLongException("Длинный текст", 4000);
            int tid;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [BlogTopics]( [ID_Blog], 
                                                                            [Text], 
                                                                            [Text_Large], 
                                                                            [Caption], 
                                                                            [isPublic], 
                                                                            [isCommentsHidden], 
                                                                            [User_ID])
                                                  VALUES(@BID, @Text, @Text_Large, @Caption, 
                                                         @isPublic, @isCommentsHidden, @UID); SELECT SCOPE_IDENTITY() as TID", conn);
                cmd.Parameters.Add("@BID", SqlDbType.Int).Value = blogID;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 500).Value = text;
                if (String.IsNullOrEmpty(longtext)) cmd.Parameters.Add("@Text_Large", SqlDbType.NVarChar).Value = DBNull.Value;
                else cmd.Parameters.Add("@Text_Large", SqlDbType.NVarChar).Value = longtext;
                cmd.Parameters.Add("@Caption", SqlDbType.NVarChar, 50).Value = caption;
                cmd.Parameters.Add("@isPublic", SqlDbType.Bit).Value = isPublic;
                cmd.Parameters.Add("@isCommentsHidden", SqlDbType.Bit).Value = hidecomments;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                tid = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Connection.Close();
            }
            if (tags.Length > 0)
            {
                foreach (String tag in tags)
                {
                    String tag1 = tag.Trim(new char[] { ' ' });
                    if (tag1 != "")
                    {
                        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand(@"INSERT INTO [BlogTags]( [ID_Topic], [TagText])
                                                          VALUES(@TID, @Tag)", conn);
                            cmd.Parameters.Add("@TID", SqlDbType.Int).Value = tid;
                            cmd.Parameters.Add("@Tag", SqlDbType.NVarChar, 25).Value = tag1;
                            cmd.Connection.Open();
                            cmd.ExecuteNonQuery();
                            cmd.Connection.Close();
                        }
                    }
                }
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Users] SET [TopicsCount] = [TopicsCount] + 1 WHERE (ID = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        public static bool AddTopic(int userID, String caption, String text, String longtext, String[] tags, bool isPublic, bool hidecomments)
        {
            if (caption.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Заголовок", 50);
            if (caption.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Заголовок", 3);
            if (text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Текст", 500);
            if (text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Текст", 3);
            if (longtext.Length > 4000) throw new Wild2.Models.Exceptions.TextLongException("Длинный текст", 4000);
            int? BlogID = null;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [ID] FROM [Blogs] WHERE ([ID_User] = @UID) and (isPublic = 'FALSE')", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    BlogID = Convert.ToInt32(reader["ID"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            if (BlogID.HasValue)
            {
                AddTopic(userID, BlogID.Value, caption, text, longtext, tags, isPublic, hidecomments);
                return false;
            }
            else
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(@"INSERT INTO [Blogs]([ID_User], [Name]) VALUES(@UID, 'Личный блог'); SELECT SCOPE_IDENTITY() as BID", conn);
                    cmd.Parameters.Add("@UID", SqlDbType.Int);
                    cmd.Parameters["@UID"].Value = userID;
                    cmd.Connection.Open();
                    BlogID = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Connection.Close();
                }
                if (BlogID.HasValue)
                {
                    AddTopic(userID, BlogID.Value, caption, text, longtext, tags, isPublic, hidecomments);
                }
                return true;
            }
        }

        public static int AddTopicSandbox(Int32? UserID, String caption, String text, String nick, String email)
        {
            int tid = -1;
            if (caption.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Заголовок", 50);
            if (caption.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Заголовок", 3);
            if (text.Length > 4000) throw new Wild2.Models.Exceptions.TextLongException("Текст", 500);
            if (text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Текст", 3);
            if (nick.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Ник", 4000);
            if (nick.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Ник", 3);
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [SandBoxTopics]([EMail], [Text], [Nick], [Caption], [UserID]) 
                                                  VALUES(@Email, @Text, @Nick, @Caption, @UID); SELECT SCOPE_IDENTITY() as TID", conn);
                cmd.Parameters.Add("@EMail", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000);
                cmd.Parameters.Add("@Caption", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                if(String.IsNullOrEmpty(email)) cmd.Parameters["@EMail"].Value = "";
                else cmd.Parameters["@EMail"].Value = email;
                cmd.Parameters["@Nick"].Value = nick;
                cmd.Parameters["@Text"].Value = text;
                cmd.Parameters["@Caption"].Value = caption;
                if (UserID.HasValue) cmd.Parameters["@UID"].Value = UserID.Value;
                else cmd.Parameters["@UID"].Value = DBNull.Value;
                cmd.Connection.Open();
                tid = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Connection.Close();
            }
            return tid;
        }

        public static List<Topic.TopicData> GetTopics(int page, int userID)
        {
            int elOnPage = 5;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]); }
            List<Topic.TopicData> topicDataList = new List<Topic.TopicData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [TID], [Text], [Caption], [Date], [UID], [Nick], [Comments_count], [Rating], [Star]  FROM            (
                                                SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY BlogTopics.ID DESC) AS num, BlogTopics.ID AS TID, BlogTopics.Text, BlogTopics.Caption, BlogTopics.Date, Users.ID AS UID, Users.Nick, BlogTopics.Comments_count, BlogTopics.Rating, BlogTopics.Star
                                                FROM         BlogTopics INNER JOIN
                                                                      Users ON BlogTopics.User_ID = Users.ID
                                                             WHERE Users.ID = @UID) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", conn);
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = (page * elOnPage);
                cmd.Parameters["@KolZap2"].Value = ((page * elOnPage) + elOnPage);
                cmd.Parameters["@UID"].Value = userID;

                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    topicDataList.Add(new Topic.TopicData()
                    {
                        Date = (DateTime)reader["date"],
                        Text = reader["Text"].ToString(),
                        TopicID = (int)reader["TID"],
                        TopicName = reader["Caption"].ToString(),
                        UserID = (int)reader["UID"],
                        UserNick = reader["Nick"].ToString(),
                        CommentsCount = Convert.ToInt32(reader["Comments_Count"]),
                        Rating = Convert.ToInt32(reader["Rating"]),
                        Star = Convert.ToBoolean(reader["Star"])
                    });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return topicDataList;
        }

        public static List<Topic.TopicData> GetFriendsAndMyTopics(int page, int userID)
        {
            int elOnPage = 5;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]); }
            List<Topic.TopicData> topicDataList = new List<Topic.TopicData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [TID], [Text], [Caption], [Date], [UID], [Nick], [Comments_count], [Rating], [Star]  FROM            (
                                                SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY BlogTopics.ID DESC) AS num, BlogTopics.ID AS TID, BlogTopics.Text, BlogTopics.Caption, BlogTopics.Date, Users.ID AS UID, Users.Nick, BlogTopics.Comments_count, BlogTopics.Rating, BlogTopics.Star
                                                FROM         BlogTopics INNER JOIN
                                                                      Users ON BlogTopics.User_ID = Users.ID
                                                             WHERE (Users.ID = @UID) OR (Users.ID IN (SELECT [Subscribes].[Kogo] FROM [Subscribes] WHERE [Subscribes].[Kto] = @UID))) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", conn);
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = (page * elOnPage);
                cmd.Parameters["@KolZap2"].Value = ((page * elOnPage) + elOnPage);
                cmd.Parameters["@UID"].Value = userID;

                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    topicDataList.Add(new Topic.TopicData()
                    {
                        Date = (DateTime)reader["date"],
                        Text = reader["Text"].ToString(),
                        TopicID = (int)reader["TID"],
                        TopicName = reader["Caption"].ToString(),
                        UserID = (int)reader["UID"],
                        UserNick = reader["Nick"].ToString(),
                        CommentsCount = Convert.ToInt32(reader["Comments_Count"]),
                        Rating = Convert.ToInt32(reader["Rating"]),
                        Star = Convert.ToBoolean(reader["Star"])
                    });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return topicDataList;
        }

        public static List<Topic.TopicData> GetTopics(int page)
        {
            int elOnPage = 5;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]); }
            List<Topic.TopicData> topicDataList = new List<Topic.TopicData>();
            using (SqlConnection connStat = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [TID], [Text], [Caption], [Date], [UID], [Nick], [Comments_count], [Rating], [Star]  FROM            (
                                                SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY BlogTopics.ID DESC) AS num, BlogTopics.ID AS TID, BlogTopics.Text, BlogTopics.Caption, BlogTopics.Date, Users.ID AS UID, Users.Nick, BlogTopics.Comments_count, BlogTopics.Rating, BlogTopics.Star
                                                FROM         BlogTopics INNER JOIN
                                                                      Users ON BlogTopics.User_ID = Users.ID
                                                WHERE  (BlogTopics.Rating > -5) AND ([isPublic] = 'TRUE')) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", connStat);
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = (page * elOnPage);
                cmd.Parameters["@KolZap2"].Value = ((page * elOnPage) + elOnPage);
               
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    topicDataList.Add(new Topic.TopicData()
                    {
                        Date = (DateTime)reader["date"],
                        Text = reader["Text"].ToString(),
                        TopicID = (int)reader["TID"],
                        TopicName = reader["Caption"].ToString(),
                        UserID = (int)reader["UID"],
                        UserNick = reader["Nick"].ToString(),
                        CommentsCount = Convert.ToInt32(reader["Comments_Count"]),
                        Rating = Convert.ToInt32(reader["Rating"]),
                        Star = Convert.ToBoolean(reader["Star"])
                    });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return topicDataList;
        }

        public static List<Topic.TopicData> GetTopicsSandbox(int page)
        {
            int elOnPage = 5;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]); }
            List<Topic.TopicData> topicDataList = new List<Topic.TopicData>();
            using (SqlConnection connStat = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [ID], [Rating], [EMail], [Shows_Count], [Comments_Count], [DateUpdate] as [Date], [Text], [Nick], [Star], [Caption], [UserID] FROM (
                                                SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY SandBoxTopics.DateUpdate DESC) AS num, SandBoxTopics.*
                                                FROM         SandBoxTopics) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", connStat);
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = (page * elOnPage);
                cmd.Parameters["@KolZap2"].Value = ((page * elOnPage) + elOnPage);

                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Topic.TopicData topic = new Topic.TopicData();
                    topic.Date = (DateTime)reader["date"];
                    topic.Text = reader["Text"].ToString();
                    topic.TopicID = (int)reader["ID"];
                    topic.TopicName = reader["Caption"].ToString();
                    if (reader["UserID"] != DBNull.Value) topic.UserID = Convert.ToInt32(reader["UserID"]);
                    else topic.UserID = -1;
                    topic.UserNick = reader["Nick"].ToString();
                    topic.CommentsCount = Convert.ToInt32(reader["Comments_Count"]);
                    topic.Rating = Convert.ToInt32(reader["Rating"]);
                    topic.Star = Convert.ToBoolean(reader["Star"]);
                    topicDataList.Add(topic);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return topicDataList;
        }

        public static List<Topic.TopicData> GetTopicsPopular(int page)
        {
            int elOnPage = 5;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]); }
            List<Topic.TopicData> topicDataList = new List<Topic.TopicData>();
            using (SqlConnection connStat = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [TID], [Text], [Caption], [Date], [UID], [Nick], [Comments_count], [Rating], [Star]  FROM   (
                                                SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY [BlogTopics].[Rating] DESC, [BlogTopics].[Date] DESC) AS num, BlogTopics.ID AS TID, BlogTopics.Text, BlogTopics.Caption, BlogTopics.Date, Users.ID AS UID, Users.Nick, BlogTopics.Comments_count, BlogTopics.Rating, BlogTopics.Star
                                                FROM         BlogTopics INNER JOIN
                                                                      Users ON BlogTopics.User_ID = Users.ID
                                                             WHERE (BlogTopics.Date > (GETDATE() - 7)) AND (BlogTopics.Rating > -5)) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", connStat); //Добавить сортировку по (countcomments*5 + countviews)
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = (page * elOnPage);
                cmd.Parameters["@KolZap2"].Value = ((page * elOnPage) + elOnPage);

                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    topicDataList.Add(new Topic.TopicData()
                    {
                        Date = (DateTime)reader["date"],
                        Text = reader["Text"].ToString(),
                        TopicID = (int)reader["TID"],
                        TopicName = reader["Caption"].ToString(),
                        UserID = (int)reader["UID"],
                        UserNick = reader["Nick"].ToString(),
                        CommentsCount = Convert.ToInt32(reader["Comments_Count"]),
                        Rating = Convert.ToInt32(reader["Rating"]),
                        Star = Convert.ToBoolean(reader["Star"])
                    });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return topicDataList;
        }
    }
}